﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Exam.Core.DBUtility;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace Exam.Core.SQLDAL
{
    public class Question {

        //返回所有
        public List<Exam.Core.Model.Question> getQuestionAllList() {
            List<Exam.Core.Model.Question> list = new List<Exam.Core.Model.Question>();

            string SQL = "SELECT * FROM Question";
            DataTable dt = DbHelperSQL.Query(SQL).Tables[0];
            foreach (DataRow dr in dt.Rows) {
                Exam.Core.Model.Question obj = new Exam.Core.Model.Question();
                obj.QuestionId = dr["QuestionId"].ToString().Trim();
                obj.QuestionContent = dr["QuestionContent"].ToString().Trim();
                obj.QuestionAnswer = dr["QuestionAnswer"].ToString().Trim();
                obj.QuestiontypeId = dr["QuestiontypeId"].ToString().Trim();
                obj.QuestionknowledgeId = dr["QuestionknowledgeId"].ToString().Trim();
                obj.DifficultyId = dr["DifficultyId"].ToString().Trim();
                obj.CourseId = dr["CourseId"].ToString().Trim();
                obj.IsShow = dr["IsShow"].ToString().Trim();
                list.Add(obj);
            }
            //可以加入缓存
            return list;

        }

        /// <summary>
        /// 通过知识点ID获取所有试题
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        internal List<Exam.Core.Model.Question> getQuestionByKnowledgeId(string KnowledgeId) {
            List<Exam.Core.Model.Question> list = new List<Exam.Core.Model.Question>();
            string SQL = "SELECT * FROM Question WHERE QuestionKnowledgeId=" + KnowledgeId;
            DataTable dt = DbHelperSQL.Query(SQL).Tables[0];
            foreach (DataRow dr in dt.Rows) {
                Exam.Core.Model.Question obj = new Exam.Core.Model.Question();
                obj.QuestionId = dr["QuestionId"].ToString().Trim();
                obj.QuestionContent = dr["QuestionContent"].ToString().Trim();
                obj.QuestionAnswer = dr["QuestionAnswer"].ToString().Trim();
                obj.QuestiontypeId = dr["QuestiontypeId"].ToString().Trim();
                obj.QuestionknowledgeId = dr["QuestionknowledgeId"].ToString().Trim();
                obj.DifficultyId = dr["DifficultyId"].ToString().Trim();
                obj.CourseId = dr["CourseId"].ToString().Trim();
                obj.IsShow = dr["IsShow"].ToString().Trim();
                list.Add(obj);
            }
            return list;
        }

        internal string getCountByCourseId(string CourseId) {
            string SQL = "SELECT Count(*) FROM Question WHERE CourseId=" + CourseId.Trim();
            object obj = DbHelperSQL.GetSingle(SQL);
            return obj.ToString();
        }

        public List<Exam.Core.Model.Question> getQuestionListByPage(int pagesize,int page, string QuestionTypeId, string QuestionDifficultyId, string QuestionKnowledgeId, string CourseId, string IsShow, string keyword) {
            List<Exam.Core.Model.Question> list = new List<Exam.Core.Model.Question>();

            //select top 15  *  from Question where QuestionId not in
            //(select top 15 QuestionId from Question where  NAME='wallimn' order by QuestionId desc) 
            //and NAME='wallimn'  order by QuestionId desc

            StringBuilder sb = new StringBuilder();
            if (QuestionTypeId.Trim() != "")
                sb.Append(" QuestionTypeId in(" + QuestionTypeId + ")");
            if (QuestionDifficultyId.Trim() != "")
                sb.Append(" and QuestionDifficultyId in(" + QuestionDifficultyId + ")");
            if (QuestionKnowledgeId.Trim() != "")
                sb.Append(" and QuestionKnowledgeId in(" + QuestionKnowledgeId + ")");
            if (CourseId.Trim() != "")
                sb.Append(" and  CourseId in(" + CourseId + ")");
            if (IsShow.Trim() != "")
                sb.Append(" and IsShow in(" + IsShow + ")");
            if (keyword.Trim() != "")
                sb.Append(" and QuestionContent like '%" + keyword.Trim() + "%'");
            sb.Append(" order by QuestionId desc");

            //string SQL = "select top " + pagesize + "  *  from Question where QuestionId not in ("
            //                + "select top " + pagesize * page + " QuestionId from Question WHERE "
            //                + sb.ToString() + " ) "
            //                + "and " + sb.ToString() + "";

            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT TOP " + pagesize + "  *  FROM Question ");
            if (page <= 0) {
                SQL.Append(" WHERE ");
                SQL.Append(sb.ToString());
            }else{
                SQL.Append(" WHERE QuestionId NOT IN ( ");
                SQL.Append(" SELECT TOP " + pagesize * page + " QuestionId FROM Question WHERE ");
                SQL.Append(sb.ToString() + " ) ");
                SQL.Append(" AND ");
                SQL.Append(sb.ToString());
            }

            DataTable dt = DbHelperSQL.Query(SQL.ToString()).Tables[0];
            foreach (DataRow dr in dt.Rows) {
                Exam.Core.Model.Question obj = new Exam.Core.Model.Question();
                obj.QuestionId = dr["QuestionId"].ToString().Trim();
                obj.QuestionContent = dr["QuestionContent"].ToString().Trim();
                obj.QuestionAnswer = dr["QuestionAnswer"].ToString().Trim();
                obj.QuestiontypeId = dr["QuestiontypeId"].ToString().Trim();
                obj.QuestionknowledgeId = dr["QuestionknowledgeId"].ToString().Trim();
                obj.DifficultyId = dr["QuestionDifficultyId"].ToString().Trim();
                obj.CourseId = dr["CourseId"].ToString().Trim();
                obj.IsShow = dr["IsShow"].ToString().Trim();
                list.Add(obj);
            }
            return list;
        }
        /// <summary>
        /// 返回题型下试题数目
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        internal string getCountByType(string typeid) {
            string SQL = "SELECT Count(*) FROM Question WHERE QuestionTypeId=" + typeid.Trim();
            object obj = DbHelperSQL.GetSingle(SQL);
            return obj.ToString();
        }

        public int getSize(string QuestionTypeId, string QuestionDifficultyId, string QuestionKnowledgeId, string CourseId, string IsShow, string keyword) {
            StringBuilder sb = new StringBuilder();
            if (QuestionTypeId.Trim() != "")
                sb.Append(" QuestionTypeId in(" + QuestionTypeId + ")");
            if (QuestionDifficultyId.Trim() != "")
                sb.Append(" and QuestionDifficultyId in(" + QuestionDifficultyId + ")");
            if (QuestionKnowledgeId.Trim() != "")
                sb.Append(" and QuestionKnowledgeId in(" + QuestionKnowledgeId + ")");
            if (CourseId.Trim() != "")
                sb.Append(" and  CourseId in(" + CourseId + ")");
            if (IsShow.Trim() != "")
                sb.Append(" and IsShow in(" + IsShow + ")");
            if (keyword.Trim() != "")
                sb.Append(" and QuestionContent like '%" + keyword.Trim() + "%'");
            //sb.Append(" order by QuestionId desc");


            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT Count(*)  FROM Question WHERE " + sb.ToString());
            
            object obj = DbHelperSQL.GetSingle(SQL.ToString());
            return int.Parse(obj.ToString());
        }

        internal string getCountByKnowledgeId(string knowId, string CourseId) {
            string SQL = "SELECT Count(*) FROM Question WHERE QuestionknowledgeId="+knowId+" AND CourseId=" + CourseId.Trim();
            object obj = DbHelperSQL.GetSingle(SQL);
            return obj.ToString();
        }



        public int Add(string QuestionContent, string QuestionAnswer, string QuestionTypeId, string QuestionDifficultyId, string QuestionKnowledgeId, int CourseId, string IsShow) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO Question(");
            strSql.Append("QuestionContent,QuestionAnswer,QuestionTypeId,QuestionDifficultyId,QuestionKnowledgeId,CourseId,IsShow)");
            strSql.Append(" VALUES (");
            strSql.Append("@QuestionContent,@QuestionAnswer,@QuestionTypeId,@QuestionDifficultyId,@QuestionKnowledgeId,@CourseId,@IsShow)");
            SqlParameter[] parameters = {
					new SqlParameter("@QuestionContent", SqlDbType.VarChar),
					new SqlParameter("@QuestionAnswer", SqlDbType.VarChar),
					new SqlParameter("@QuestionTypeId", SqlDbType.Int,4),
					new SqlParameter("@QuestionDifficultyId", SqlDbType.Int,4),
					new SqlParameter("@QuestionKnowledgeId", SqlDbType.Int,4),
					new SqlParameter("@CourseId", SqlDbType.Int,4),
					new SqlParameter("@IsShow", SqlDbType.Int,4)};
            parameters[0].Value = QuestionContent;
            parameters[1].Value = QuestionAnswer;
            parameters[2].Value = int.Parse(QuestionTypeId);
            parameters[3].Value = int.Parse(QuestionDifficultyId);
            parameters[4].Value = int.Parse(QuestionKnowledgeId);
            parameters[5].Value = CourseId;
            parameters[6].Value = int.Parse(IsShow);

            //DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            int result = 0;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0) {
                result = DbHelperSQL.GetMaxID("QuestionId", "Question");
                if (result > 0) {
                    return result;
                }
            }
            return 0;
        }



        public int AddOpts(int questionid, string[] optsText) {
            int result = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO QuestionOpts(");
            strSql.Append("QuestionOptsValue,QuestionOptsText,QuestionId)");
            strSql.Append(" VALUES (");
            strSql.Append("@QuestionOptsValue,@QuestionOptsText,@QuestionId)");
            SqlParameter[] parameters = {
					new SqlParameter("@QuestionOptsValue", SqlDbType.VarChar,50),
					new SqlParameter("@QuestionOptsText", SqlDbType.VarChar,50),
					new SqlParameter("@QuestionId", SqlDbType.Int,4)};

            string[] optsValues = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M","N","O","P","Q","R"};

            for (int i = 0; i < optsText.Length; i++) {
                parameters[0].Value = optsValues[i].Trim();
                parameters[1].Value = optsText[i].Trim();
                parameters[2].Value = questionid;
                result = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);

            }

            return result;
            //DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        public bool Del(string ids, int cid) {
            String SQL = "DELETE FROM Question WHERE QuestionId IN (" + ids.Trim(',') + ") AND CourseId=" + cid;

            string SQL2 = "DELETE FROM QuestionOpts WHERE QuestionId IN (" + ids.Trim(',') + ")";

            bool result = false;
            try{
                DbHelperSQL.ExecuteSql(SQL);
                DbHelperSQL.ExecuteSql(SQL2);
                result = true;
            }
            catch (System.Exception ex){
                result = false;
            }

            return result;
        }
    }
}
